package com.fengwei.service;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.StrUtil;
import com.fengwei.dao.MdmsDatabaseMapper;
import com.fengwei.dao.MdmsDatachangeApplyMapper;
import com.fengwei.executor.AbstractDbExecutor;
import com.fengwei.form.MdmsDatabase;
import com.fengwei.form.MdmsDatachangeApply;
import com.fengwei.form.MdmsUser;
import com.fengwei.requestdata.MdmsDatachangeApplyAddData;
import com.fengwei.requestdata.MdmsDatachangeApplyCheckData;
import com.fengwei.requestdata.MdmsDatachangeApplyDelData;
import com.fengwei.requestdata.MdmsDatachangeApplyEditData;
import com.fengwei.requestdata.MdmsDatachangeApplyPagingListData;
import com.fengwei.util.MdmsConsts;
import com.fengwei.util.PltResult;
import com.fengwei.util.SnowflakeIdWorker;
import com.fengwei.util.SpringContextUtil;
import com.fengwei.util.SqlUtil;
import com.fengwei.util.VerifyException;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.StringTokenizer;

/**
 * @Classname MdmsDatachangeApplyService
 * @Description 数据变更申请Service
 * @Date 2020/11/25 10:51
 * @Author ludexin
 */
@Service
public class MdmsDatachangeApplyService {
    private final static Logger logger = LoggerFactory.getLogger(MdmsDatachangeApplyService.class);

    @Autowired
    private MdmsDatachangeApplyMapper mdmsDatachangeApplyMapper;

    @Autowired
    private MdmsDatabaseMapper mdmsDatabaseMapper;

    @Autowired
    private SnowflakeIdWorker snowflakeIdWorker;

    /**
     * @param mdmsDatachangeApplyPagingListData, mdmsUser
     * @return java.util.Map<java.lang.String, java.lang.Object>
     * @throws
     * @author ludexin
     * @Description 分页查询数据变更申请记录
     * @date 2020/11/25 11:04
     */
    public Map<String, Object> pagingList(MdmsDatachangeApplyPagingListData mdmsDatachangeApplyPagingListData, MdmsUser mdmsUser) {
        Map<String, Object> mapPagingList = new HashMap<>(8);
        logger.debug("Usertype: [{}], userId: [{}]", mdmsUser.getUserType(), mdmsUser.getId());
        if (MdmsConsts.USER_TYPE_99.equals(mdmsUser.getUserType())
                || MdmsConsts.USER_TYPE_0.equals(mdmsUser.getUserType())) {
            // 管理员、普通用户只能查看自己的数据变更申请
            mapPagingList.put("applyUserId", mdmsUser.getId());
        }

        PageHelper.startPage(mdmsDatachangeApplyPagingListData.getPage(), mdmsDatachangeApplyPagingListData.getLimit());
        PageHelper.orderBy("id desc");
        Page<MdmsDatachangeApply> list = mdmsDatachangeApplyMapper.selectByPage(mapPagingList);

        Map<String, Object> mapReturn = new HashMap<>(8);
        mapReturn.put("total", list.getTotal());
        mapReturn.put("limit", mdmsDatachangeApplyPagingListData.getLimit());
        mapReturn.put("page", mdmsDatachangeApplyPagingListData.getPage());
        mapReturn.put("list", list.getResult());
        return mapReturn;
    }

    /**
     * @param mdmsDatachangeApplyAddData, mdmsUser
     * @return java.util.Map<java.lang.String, java.lang.Object>
     * @throws
     * @author ludexin
     * @Description 新增数据变更申请
     * @date 2020/11/25 16:02
     */
    public Map<String, Object> add(MdmsDatachangeApplyAddData mdmsDatachangeApplyAddData, MdmsUser mdmsUser) throws Exception {
        Map<String, Object> returnData = new HashMap<>();

        String dateStr = DateUtil.format(DateUtil.date(), "yyyyMMddHHmmss");

        MdmsDatachangeApply mdmsDatachangeApply = new MdmsDatachangeApply();
        mdmsDatachangeApply.setId(snowflakeIdWorker.nextId());
        mdmsDatachangeApply.setApplyUserId(mdmsUser.getId());
        mdmsDatachangeApply.setApplyUserName(mdmsUser.getUserName());
        mdmsDatachangeApply.setApplyDate(dateStr.substring(0, 8));
        mdmsDatachangeApply.setApplyTime(dateStr.substring(8, 14));
        mdmsDatachangeApply.setDatabaseId(Long.valueOf(mdmsDatachangeApplyAddData.getDatabaseId()));
        mdmsDatachangeApply.setDatabaseName(mdmsDatachangeApplyAddData.getDatabaseName());
        mdmsDatachangeApply.setApplyContent(mdmsDatachangeApplyAddData.getApplyContent());
        mdmsDatachangeApply.setBackContent(mdmsDatachangeApplyAddData.getBackContent());
        mdmsDatachangeApply.setApplyStatus(MdmsConsts.APPLY_STATUS_0);
        mdmsDatachangeApply.setRunStatus(MdmsConsts.RUN_STATUS_0);
        mdmsDatachangeApply.setAffectLineNum(Integer.valueOf(mdmsDatachangeApplyAddData.getAffectLineNum()));
        mdmsDatachangeApplyMapper.insert(mdmsDatachangeApply);

        return returnData;
    }

    /**
     * @param mdmsDatachangeApplyEditData
     * @return java.util.Map<java.lang.String, java.lang.Object>
     * @throws
     * @author ludexin
     * @Description 修改数据变更申请
     * @date 2020/11/25 16:09
     */
    public Map<String, Object> edit(MdmsDatachangeApplyEditData mdmsDatachangeApplyEditData) {
        Map<String, Object> returnData = new HashMap<>();
        Map mapSelectByPrimaryKey = new HashMap();
        mapSelectByPrimaryKey.put("id", mdmsDatachangeApplyEditData.getId());
        MdmsDatachangeApply mdmsDatachangeApply = mdmsDatachangeApplyMapper.selectByPrimaryKey(mapSelectByPrimaryKey);
        if (null == mdmsDatachangeApply) {
            // 申请记录不存在
            throw new VerifyException(PltResult.RESULT_CF311);
        } else if (MdmsConsts.APPLY_STATUS_0 != mdmsDatachangeApply.getApplyStatus()) {
            // 非申请中状态记录不允许修改
            throw new VerifyException(PltResult.RESULT_CF312);
        } else {
            MdmsDatachangeApply applyUpdate = new MdmsDatachangeApply();
            applyUpdate.setId(mdmsDatachangeApply.getId());
            applyUpdate.setDatabaseId(Long.valueOf(mdmsDatachangeApplyEditData.getDatabaseId()));
            applyUpdate.setDatabaseName(mdmsDatachangeApplyEditData.getDatabaseName());
            applyUpdate.setApplyContent(mdmsDatachangeApplyEditData.getApplyContent());
            applyUpdate.setBackContent(mdmsDatachangeApplyEditData.getBackContent());
            applyUpdate.setAffectLineNum(Integer.valueOf(mdmsDatachangeApplyEditData.getAffectLineNum()));
            mdmsDatachangeApplyMapper.update(applyUpdate);
        }

        return returnData;
    }

    /**
     * @param mdmsDatachangeApplyDelData
     * @return java.util.Map<java.lang.String, java.lang.Object>
     * @throws
     * @author ludexin
     * @Description 删除数据变更申请
     * @date 2020/11/25 16:11
     */
    public Map<String, Object> del(MdmsDatachangeApplyDelData mdmsDatachangeApplyDelData) {
        Map<String, Object> returnData = new HashMap<>();
        Map mapSelectByPrimaryKey = new HashMap();
        mapSelectByPrimaryKey.put("id", mdmsDatachangeApplyDelData.getId());
        MdmsDatachangeApply mdmsDatachangeApply = mdmsDatachangeApplyMapper.selectByPrimaryKey(mapSelectByPrimaryKey);
        if (null == mdmsDatachangeApply) {
            // 申请不存在
            throw new VerifyException(PltResult.RESULT_CF311);
        } else if (MdmsConsts.APPLY_STATUS_0 != mdmsDatachangeApply.getApplyStatus()) {
            // 非申请中状态记录不允许删除
            throw new VerifyException(PltResult.RESULT_CF312);
        } else {
            MdmsUser mdmsUserDel = new MdmsUser();
            mdmsUserDel.setId(mdmsDatachangeApply.getId());
            mdmsDatachangeApplyMapper.delete(mdmsUserDel);
        }

        return returnData;
    }

    public Map<String, Object> check(MdmsDatachangeApplyCheckData mdmsDatachangeApplyCheckData, MdmsUser mdmsUser) throws Exception {
        Map<String, Object> returnData = new HashMap<>();
        Map mapSelectByPrimaryKey = new HashMap();

        if (!MdmsConsts.USER_TYPE_1.equals(mdmsUser.getUserType())) {
            throw new VerifyException(PltResult.RESULT_CF313);
        }

        mapSelectByPrimaryKey.put("id", mdmsDatachangeApplyCheckData.getId());
        MdmsDatachangeApply mdmsDatachangeApply = mdmsDatachangeApplyMapper.selectByPrimaryKey(mapSelectByPrimaryKey);
        if (null == mdmsDatachangeApply) {
            // 申请不存在
            throw new VerifyException(PltResult.RESULT_CF311);
        } else if (MdmsConsts.APPLY_STATUS_0 != mdmsDatachangeApply.getApplyStatus()) {
            // 非申请中状态记录不允许审核
            throw new VerifyException(PltResult.RESULT_CF312);
        } else {
            String dateStr = DateUtil.format(DateUtil.date(), "yyyyMMddHHmmss");

            /*校验SQL*/
            List<String> sqlList = new ArrayList<>();
            String applyContent = mdmsDatachangeApply.getApplyContent();
            StringTokenizer stn = new StringTokenizer(applyContent, ";");
            while (stn.hasMoreTokens()) {
                String sql = stn.nextToken();
                sql = sql.trim();
                sql = SqlUtil.trimLeft(sql);
                if (StrUtil.hasEmpty(sql)) {
                    //如果仅仅是多了分号，则忽略
                    continue;
                }
                if (sql.toLowerCase().startsWith("insert") || sql.toLowerCase().startsWith("update") || sql.toLowerCase().startsWith("delete")) {
                    sqlList.add(sql);
                } else {
                    throw new VerifyException(PltResult.RESULT_CF314);
                }
            }

            /*1-先更新审核状态*/
            MdmsDatachangeApply applyUpdate = new MdmsDatachangeApply();
            applyUpdate.setId(mdmsDatachangeApply.getId());
            applyUpdate.setApplyStatus(Short.parseShort(mdmsDatachangeApplyCheckData.getApplyStatus()));
            applyUpdate.setRunStatus(MdmsConsts.RUN_STATUS_0);
            applyUpdate.setCheckUserId(mdmsUser.getId());
            applyUpdate.setCheckUserName(mdmsUser.getUserName());
            applyUpdate.setCheckDate(dateStr.substring(0, 8));
            applyUpdate.setCheckTime(dateStr.substring(8, 14));
            mdmsDatachangeApplyMapper.update(applyUpdate);

            if (MdmsConsts.APPLY_STATUS_1 == Short.parseShort(mdmsDatachangeApplyCheckData.getApplyStatus())) {
                /*2-然后执行SQL*/
                //TODO:回退SQL先不执行，以后再说
                Map mapSelectDatabaseByPrimaryKey = new HashMap();
                mapSelectDatabaseByPrimaryKey.put("id", mdmsDatachangeApply.getDatabaseId());
                MdmsDatabase mdmsDatabase = mdmsDatabaseMapper.selectByPrimaryKey(mapSelectDatabaseByPrimaryKey);
                if (null == mdmsDatabase) {
                    // 数据库不存在
                    throw new VerifyException(PltResult.RESULT_CF300);
                } else {
                    short runStatus = MdmsConsts.RUN_STATUS_1;
                    AbstractDbExecutor abstractDbExecutor = (AbstractDbExecutor) SpringContextUtil.getBean(mdmsDatabase.getDatabaseType() + "Executor");
                    String url = abstractDbExecutor.genUrl(mdmsDatabase.getDatabaseType(), mdmsDatabase.getDatabaseHost(), mdmsDatabase.getDatabasePort(),
                            mdmsDatabase.getDatabaseDbname());
                    Connection connection = null;
                    try {
                        connection = abstractDbExecutor.getConnection(mdmsDatabase.getId() + "", url, mdmsDatabase.getDatabaseUser(), mdmsDatabase.getDatabasePassword());
                        abstractDbExecutor.executeInsertUpdateDeleteSqlAfterCheck(connection, sqlList, mdmsDatachangeApply.getAffectLineNum());
                        updateRunStatus(mdmsDatachangeApply, runStatus);
                    } catch (Exception e) {
                        runStatus = MdmsConsts.RUN_STATUS_2;
                        updateRunStatus(mdmsDatachangeApply, runStatus);
                        logger.error(e.getMessage(), e);
                        if (e instanceof VerifyException) {
                            throw e;
                        } else {
                            VerifyException verifyException = new VerifyException(PltResult.RESULT_CF304);
                            verifyException.setErrorDesc(PltResult.RESULT_CF304.getpName() + ":" + e.getMessage());
                            // 数据库不存在
                            throw verifyException;
                        }
                    } finally {
                        abstractDbExecutor.closeConnection(connection);
                    }


                }
            }

            return returnData;
        }
    }

    private void updateRunStatus(MdmsDatachangeApply mdmsDatachangeApply, short runStatus) {
        MdmsDatachangeApply applyUpdate;
        /*3-最后更新执行状态*/
        applyUpdate = new MdmsDatachangeApply();
        applyUpdate.setId(mdmsDatachangeApply.getId());
        applyUpdate.setRunStatus(runStatus);
        mdmsDatachangeApplyMapper.update(applyUpdate);
    }
}
